CREATE VIEW [dbo].[vSoaInvoiceLineARBase] WITH SCHEMABINDING
AS
SELECT CAST(il.[REFERENCE_NUM] AS VARCHAR(15)) + ':' + CAST(il.[LINE_NUM] AS VARCHAR(15)) AS InvoiceLineId,
       CAST(il.[REFERENCE_NUM] AS VARCHAR(20)) AS InvoiceId,
       il.[LINE_NUM] AS LineNumber,
       il.[PRODUCT_CODE] AS ItemId,
       il.[ST_ID] AS ShipToPartyId,
       il.[BILL_QTY] AS Quantity,
       CAST(CASE WHEN il.[BILL_QTY] > 0 THEN (il.[CHARGES]/il.[BILL_QTY]) ELSE il.[CHARGES] END AS MONEY) AS UnitPrice,
       il.[CHARGES] AS ExtendedAmount,
       il.[CREDITS] AS PaymentAdjustmentAmount,
       il.[REFERENCE_NUM]
  FROM [dbo].[Invoice_Lines] il

GO
CREATE UNIQUE CLUSTERED INDEX [PK_vSoaInvoiceLineARBase] ON [dbo].[vSoaInvoiceLineARBase] ([InvoiceLineId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_vSoaInvoiceLineARBase_InvoiceId] ON [dbo].[vSoaInvoiceLineARBase] ([InvoiceId], [LineNumber]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_vSoaInvoiceLineARBase_ShipToPartyId] ON [dbo].[vSoaInvoiceLineARBase] ([ShipToPartyId]) ON [PRIMARY]
GO